# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
import pymysql
from itemadapter import ItemAdapter


class ZhilianPipeline:
    def process_item(self, item, spider):
        return item

class MySQLPipeline:
    def open_spider(self, spider):
        # 读取setting.pu配置
        host = spider.settings.get('MYSQL_DB_HOST')
        port = spider.settings.get('MYSQL_DB_POST')
        dbname = spider.settings.get('MYSQL_DB_NAME')
        user = spider.settings.get('MYSQL_DB_USER')
        pwd = spider.settings.get('MYSQL_DB_PASSWORD')        # 创建数据库链接

        self.db_coon = pymysql.connect(host=host, port=port, user=user, db=dbname, password=pwd)
        # 打开游标
        self.db_cur = self.db_coon.cursor()

    def process_item(self, item, spider):
        values = (
            item["company"],
            item["wages"],
            item["work_name"],
            item["address"],
            item["experience"],
            item["education"],
 )      # 与占位符%s对应的数据
        # sql语句，数据库部分使用占位符%s代替
        sql = """insert into qiancheng(company,wages,work_name,address,experience,education)
                                   values(%s,%s,%s,%s,%s,%s)"""
        self.db_cur.execute(sql, values)  # 执行sql语句
        return item

    def close_spider(self, spider):
        self.db_coon.commit()  # 提交事务所
        self.db_cur.close()  # 关闭游标
        self.db_coon.close()  # 关闭数据库链接